package com.huabo.audit.oracle.mapper;

import java.math.BigDecimal;

import com.huabo.audit.oracle.entity.TblOrganization;
import com.huabo.audit.oracle.entity.TblStaff;
import com.huabo.audit.util.PageInfo;

public class TblStaffMapperSqlConfig {
	
	public String selectListByPageInfo(PageInfo<TblStaff> pageInfo,Integer orgid) {
		
		StringBuffer sb = new StringBuffer("SELECT * FROM "
				+ "(SELECT T1.*,ROWNUM RN  FROM "
				+ "(SELECT TNA.*,ol.ORGNAME "
				+ "FROM TBL_STAFF TNA "
				+ "LEFT JOIN TBL_ORGANIZATION ol ON ol.ORGID = TNA.ORGID "
				+ " WHERE TNA.ORGID in (select ORGID from TBL_ORGANIZATION org "
				+ " where 1=1 ");
				
		if(null != orgid){
			sb.append(" AND org.FATHERORGID="+orgid);
		}
		sb.append(" AND org.ORGTYPE=0 ) ");
		
		sb.append(" ORDER BY TNA.STAFFID DESC) T1 WHERE ROWNUM <= "+(pageInfo.getCurrentRecord()+pageInfo.getPageSize())+" ) T2 WHERE T2.RN > "+pageInfo.getCurrentRecord());
		return sb.toString();
	}
	
	public String selectCountByPageInfo(PageInfo<TblStaff> pageInfo,Integer orgid) {
		StringBuffer sb = new StringBuffer("SELECT COUNT(0) "
				+ "FROM TBL_STAFF TNA "
				+ "LEFT JOIN TBL_ORGANIZATION ol ON ol.ORGID = TNA.ORGID "
				+ " WHERE TNA.ORGID in (select ORGID from TBL_ORGANIZATION org "
				+ " where 1=1 ");
				
		if(null != orgid){
			sb.append(" AND org.FATHERORGID="+orgid);
		}
		sb.append(" AND org.ORGTYPE=0 ) ");
		
		
		return sb.toString();
	}
	
	
	public String findAllPageBeanPid(PageInfo<TblStaff> pageInfo, TblOrganization attribute){
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (select STAFFID,REALNAME,STA.ORGID,ORG.ORGNAME,STA.ADDRESS,STA.EMAIL,username from TBL_STAFF STA INNER JOIN TBL_ORGANIZATION ORG ON STA.ORGID=ORG.ORGID WHERE 1=1 ");
        if(attribute.getOrgtype()!=null && attribute.getOrgtype().toString().equals("0")){
            sqlSb.append("AND STA.ORGID ="+attribute.getOrgid()+" AND (STA.STATUS is NULL or STA.STATUS != 0)");
        }else {
            sqlSb.append("AND STA.ORGID in (select ORGID from TBL_ORGANIZATION org where FATHERORGID="+attribute.getOrgid()+" AND ORGTYPE=0 ) AND (STA.STATUS is NULL or STA.STATUS != 0)");
        }

        sqlSb.append(" order by STAFFID desc) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql.toString();
    }
    public String findAllCountPageBeanPid(PageInfo<TblStaff> pageInfo, TblOrganization attribute){
        StringBuffer sqlSb = new StringBuffer("select COUNT(*) from TBL_STAFF STA INNER JOIN TBL_ORGANIZATION ORG ON STA.ORGID=ORG.ORGID WHERE 1=1 ");
        if(attribute.getOrgtype()!=null && attribute.getOrgtype().toString().equals("0")){
            sqlSb.append("AND STA.ORGID ="+attribute.getOrgid()+" AND (STA.STATUS is NULL or STA.STATUS != 0)");
        }else {
            sqlSb.append("AND STA.ORGID in (select ORGID from TBL_ORGANIZATION org where FATHERORGID="+attribute.getOrgid()+" AND ORGTYPE=0 ) AND (STA.STATUS is NULL or STA.STATUS != 0)");
        }
        return sqlSb.toString();
    }
    
    
    public String selectByOrgidListPageInfo(PageInfo<TblStaff> pageInfo,BigDecimal orgid,String username,String realname) {
		
		StringBuffer sb = new StringBuffer("SELECT * FROM  (SELECT T1.*,ROWNUM RN  FROM ");
		sb.append("(select DISTINCT * from TBL_STAFF WHERE (STAFFID in (SELECT DISTINCT TE.STAFFID from TBL_NBSJ_TEAMSTAFF te LEFT JOIN TBL_NBSJ_PRO_TEAM pt ON TE.TEAMID=PT.TEAMID WHERE PROJECTID in (SELECT PROJECTID  from TBL_NBSJ_PROJECT WHERE STATUS=4 AND ORGID="+orgid+" )) ");
		sb.append(" OR STAFFID IN (SELECT PMID from TBL_NBSJ_PROJECT WHERE STATUS=4 AND ORGID="+orgid+"))  ");
		if(username!=null && !username.equals("")) {
			sb.append("  and USERNAME like '%"+username+"%' ");
		}
		if(realname!=null && !realname.equals("")) {
			sb.append(" and REALNAME like '%"+realname+"%' ");
		}
		sb.append(" ORDER BY STAFFID DESC) T1 WHERE ROWNUM <= "+(pageInfo.getCurrentRecord()+pageInfo.getPageSize())+" ) T2 WHERE T2.RN > "+pageInfo.getCurrentRecord());
		return sb.toString();
	}
	
    public String selectByOrgidCountPageInfo(BigDecimal orgid,String username,String realname) {
		
		StringBuffer sb = new StringBuffer("SELECT count(*) FROM (  ");
		sb.append("select DISTINCT * from TBL_STAFF WHERE (STAFFID in (SELECT DISTINCT TE.STAFFID from TBL_NBSJ_TEAMSTAFF te LEFT JOIN TBL_NBSJ_PRO_TEAM pt ON TE.TEAMID=PT.TEAMID WHERE PROJECTID in (SELECT PROJECTID  from TBL_NBSJ_PROJECT WHERE STATUS=4 AND ORGID="+orgid+" )) ");
		sb.append(" OR STAFFID IN (SELECT PMID from TBL_NBSJ_PROJECT WHERE STATUS=4 AND ORGID="+orgid+"))  ");
		if(username!=null && !username.equals("")) {
			sb.append(" and USERNAME like '%"+username+"%' ");
		}
		if(realname!=null && !realname.equals("")) {
			sb.append(" and REALNAME like '%"+realname+"%' ");
		}
		sb.append(" ORDER BY STAFFID DESC) T1   ");
		return sb.toString();
	}
    
}
